<?php
/**
 * Created by PhpStorm.
 * User: Administrator
 * Date: 2020/8/27
 * Time: 10:25
 */

namespace app\admin\controller;


use app\common\controller\Comm;
use app\common\model\Stationorder;

class Count extends Admin
{
    /**
     * 每月统计
     * @return \think\response\Json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getMonthList(){
        $page = input('page', 1);
        $limit = input('limit', 10);
        $sqlCon = $this->getMonthTimes();
        $order_model = new Stationorder();
        $count = $order_model->alias('o')
            ->join('gasstation g','g.id = o.oils_stationid','left')
            ->join('account_count a','a.id = o.totall_account_id','left')
            ->where($sqlCon['param'])
            ->group($sqlCon['groupStr'])
            ->count();
        if($page > ceil($count/$limit)){
            $page = ceil($count/$limit);
        }
        $order_lst = $order_model->alias('o')
            ->join('gasstation g','g.id = o.oils_stationid','left')
            ->join('account_count a','a.id = o.totall_account_id','left')
            ->field($sqlCon['fieldStr'])
            ->where($sqlCon['param'])
            ->group($sqlCon['groupStr'])
            ->page($page,$limit)
            ->order($sqlCon['groupStr'])
            ->select();
        $pagobj = $order_model->alias('o')->where($sqlCon['param'])->field('sum(total_price) as total_price,count(id) as order_num,sum(count) as total_count')->find();
        $pagobj['code'] = 0 ;
        $pagobj['msg'] = '';
        $pagobj['page'] = $page ;
        $pagobj['count'] = $count;
        $pagobj['data'] = $order_lst;
        return json($pagobj);
    }

    /**
     * 每月统计导出
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function exportMonthData(){
        $sqlCon = $this->getMonthTimes();
        $time_type = input('time_type',1); // 1-物流汇总  2-油站汇总
        $order_month =  input('order_month', date('Y-m'), 'trim');

        $order_model = new Stationorder();
        $order_lst = $order_model->alias('o')
            ->join('gasstation g','g.id = o.oils_stationid','left')
            ->join('account_count a','a.id = o.totall_account_id','left')
            ->field($sqlCon['fieldStr'])
            ->where($sqlCon['param'])
            ->group($sqlCon['groupStr'])
            ->order($sqlCon['groupStr'])
            ->select();
        if($time_type == 1){
            $table_title = ['所属物流','区域', '品牌','单数', '合计升数（L）', '合计金额（元）'];
            $table_value = ['company_name','province','brand_flow', 'number', 'count', 'total_price'];
            //导出
            $filename = '物流汇总'.date('Y年m月',strtotime($order_month));
        }else{
            $table_title = ['油站名称','区域', '品牌','单数', '合计升数（L）', '合计金额（元）'];
            $table_value = ['station_name','province','brand_flow', 'number', 'count', 'total_price'];
            //导出
            $filename = '油站汇总'.date('Y年m月',strtotime($order_month));
        }
        export_excle($order_lst, $table_title, $table_value, $filename);
    }
    /**
     * 月统计筛选信息
     * @return mixed
     */
    public function getMonthTimes(){
        $company_id =  input('company_id');
        $station_id =  input('station_id');
        $order_month =  input('order_month', '', 'trim');
        $time_type = input('time_type',1); // 1-物流汇总  2-油站汇总
        if(!$order_month){
            $order_month = date('Y-m');
        }
        $param['o.state'] = 0;
        if ($company_id) {
            $param['o.totall_account_id'] = $company_id;
        }
        if($station_id){
            $param['o.oils_stationid'] = $station_id;
        }
        $time_start1 = date("Y-m-01", strtotime($order_month)).' 00:00:00';
        $time_end1 =  date('Y-m-t', strtotime($order_month)).' 23:59:59';
        $param['o.add_datetime'] = ['between', [$time_start1, $time_end1]];
        $sqlCon = [];
        if($time_type==1){
            $sqlCon['groupStr'] = 'o.totall_account_id,g.province,g.brand_flow';
            $sqlCon['fieldStr'] ='a.company_name,g.province,g.brand_flow,count(*) as number,sum(o.count) as count,sum(o.total_price) as total_price';
            $sqlCon['orderStr'] ='o.totall_account_id';
        }else{
            $sqlCon['groupStr']='g.province,g.brand_flow,g.station_name';
            $sqlCon['fieldStr'] ='g.station_name,g.province,g.brand_flow,count(*) as number,sum(o.count) as count,sum(o.total_price) as total_price';
            $sqlCon['orderStr'] ='g.province,count desc';
        }
        $sqlCon['param'] = $param;
        return $sqlCon;
    }
    /**
     * 获取每日数据统计
     * @return \think\response\Json
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function getDayList(){
        $page = input('page', 1);
        $limit = input('limit', 10);
        $param = $this->getTimes();
        $order_model = new Stationorder();
        $count = $order_model->where($param)->group("DATE_FORMAT(add_datetime,'%Y-%m-%d'),oils_stationid, totall_account_id")->count();
        if($page > ceil($count/$limit)){
            $page = ceil($count/$limit);
        }
        $order_lst = $order_model->with(['station','company'])
            ->field("DATE_FORMAT(add_datetime,'%Y-%m-%d') as 'add_datetime', count(id) as order_num, sum(total_price) as sum_price, sum(count) as sum_count, oils_stationid, totall_account_id")
            ->where($param)
            ->group("DATE_FORMAT(add_datetime,'%Y-%m-%d'),oils_stationid, totall_account_id")
            ->order('add_datetime desc')
            ->page($page,$limit)
            ->select();
        $pagobj = $order_model->where($param)->field('sum(total_price) as total_price,count(id) as order_num,sum(count) as total_count')->find();
        $pagobj['code'] = 0 ;
        $pagobj['msg'] = '';
        $pagobj['page'] = $page ;
        $pagobj['count'] = $count;
        $pagobj['data'] = $order_lst;
        return json($pagobj);
    }

    /**
     * 导出每日数据统计
     * @throws \think\db\exception\DataNotFoundException
     * @throws \think\db\exception\ModelNotFoundException
     * @throws \think\exception\DbException
     */
    public function exportDayList(){
        $param = $this->getTimes();
        $order_model = new Stationorder();
        $order_lst = $order_model->with(['station','company'])
            ->field("DATE_FORMAT(add_datetime,'%Y-%m-%d') as 'add_datetime', count(id) as order_num, sum(total_price) as sum_price, sum(count) as sum_count, oils_stationid, totall_account_id")
            ->where($param)
            ->group("DATE_FORMAT(add_datetime,'%Y-%m-%d'),oils_stationid, totall_account_id")
            ->order('add_datetime desc')
            ->select();
        $table_title = ['下单时间', '物流公司', '油站名称', '单数', '加油升数(升)', '总价(元)'];
        $table_value = ['add_datetime','company_name','station_name','order_num', 'sum_count','sum_price'];
        //导出
        $filename = '每日订单明细' . date('Ymd');
        export_excle($order_lst, $table_title, $table_value, $filename);
    }

    /**
     * 获取每日统计筛选信息
     * @return array
     */
    public function getTimes(){
        $time_start = input('time_start', '', 'trim');
        $time_end = input('time_end', '', 'trim');
        $vehicle_license_num = input('vehicle_license_num', '', 'trim');
        $company_id =  input('company_id');
        $station_id =  input('station_id');
        $param = [];  // 参数
        if ($company_id) {
            $param['totall_account_id'] = $company_id;
        }
        $param['state'] = 0;
        if($station_id){
            $param['oils_stationid'] = $station_id;
        }
        if ($vehicle_license_num){
            $param['vehicle_license_num'] = ['like', '%'.$vehicle_license_num.'%'];
        }
        if($time_start && $time_end){
            $time_start = date("Y-m-d", strtotime($time_start)).' 00:00:00';
            $time_end = date("Y-m-d", strtotime($time_end)).' 23:59:59';
            $param['add_datetime'] = ['between', [$time_start, $time_end]];
        }elseif($time_start && !$time_end){
            $time_start = date("Y-m-d", strtotime($time_start)).' 00:00:00';
            $param['add_datetime'] = ['>=', $time_start];
        }elseif(!$time_start && $time_end){
            $time_end = date("Y-m-d", strtotime($time_end)).' 23:59:59';
            $param['add_datetime'] = ['<=', $time_end];
        }
        return $param;
    }
}